/*
**    Generate an ansi name that is unique in the dtproperties.value column
*/
create procedure dbo.dt_generateansiname(@name varchar(255) output)
as
    declare @prologue varchar(20)
    declare @indexstring varchar(20)
    declare @index integer

    set @prologue = 'MSDT-A-'
    set @index = 1

    while 1 = 1
    begin
        set @indexstring = cast(@index as varchar(20))
        set @name = @prologue + @indexstring
        if not exists (select value from dtproperties where value = @name)
            break
         
        set @index = @index + 1

        if (@index = 10000)
            goto TooMany
    end

Leave:

    return

TooMany:

    set @name = 'DIAGRAM'
    goto Leave
GO
GRANT EXECUTE ON  [dbo].[dt_generateansiname] TO [public]
GO